const router = require('koa-router')()
var multer = require('multer')
var fs = require('fs');
const path = require('path');

//查询全部景点
router.get('/getspot', async (ctx, next) => {
    let sql = "select * from spot order by id DESC";
    let result = await ctx.db.EXCUTE(sql);
    for (var i = 0; i < result.length; i++) {
        //修改城市信息
        let citysql = "select name from city where id=?"
        let name = await ctx.db.EXCUTE(citysql, [result[i].city]);
        result[i].city = name[0].name;
        //评分
        let valuesql = "select avg(value) as data from rate where spotid=?";
        let value = await ctx.db.EXCUTE(valuesql, [result[i].id]);
        result[i].value = value[0].data;
    }
    ctx.body = { data: result, status: "获取景点信息成功" }
})

//搜索某个景点
router.post('/selspot', async (ctx, next) => {
    let name = ctx.request.body.name;
    let sql = `select * from spot where name like'%${name}%' order by id DESC`;
    let result = await ctx.db.EXCUTE(sql, [name]);
    if (result.length != 0) {
        for (var i = 0; i < result.length; i++) {
            //修改城市信息
            let citysql = "select name from city where id=?"
            let name = await ctx.db.EXCUTE(citysql, [result[i].city]);
            result[i].city = name[0].name;
            //评分
            let valuesql = "select avg(value) as data from rate where spotid=?";
            let value = await ctx.db.EXCUTE(valuesql, [result[i].id]);
            result[i].value = value[0].data;
        }
        ctx.body = { code: 200, status: "搜索信息成功", data: result }
    } else {
        let sql = `select id from city where name like'%${name}%'`;
        let result = await ctx.db.EXCUTE(sql, [name]);
        if (result.length != 0) {
            let selsql = `select * from spot where city=?`;
            let selresult = await ctx.db.EXCUTE(selsql, [result[0].id]);
            if (selresult.length != 0) {
                for (var i = 0; i < selresult.length; i++) {
                    //修改城市信息
                    let citysql = "select name from city where id=?"
                    let name = await ctx.db.EXCUTE(citysql, [selresult[i].city]);
                    selresult[i].city = name[0].name;
                    //评分
                    let valuesql = "select avg(value) as data from rate where spotid=?";
                    let value = await ctx.db.EXCUTE(valuesql, [result[i].id]);
                    result[i].value = value[0].data;
                }
                ctx.body = { code: 200, status: "搜索信息成功", data: selresult }
            } else {
                ctx.body = { code: 400, status: "输入信息错误,无数据", data: [] }
            }
        } else {
            ctx.body = { code: 400, status: "输入信息错误,无数据", data: [] }
        }
    }
})

//添加景点
//上传图片
const upload = multer({ dest: 'public/spotimg' })
upload.single("file")
router.post('/subspot', async (ctx, next) => {
    let res = ctx.request.body
    let file = res.image;
    let imgurl = '';
    if (file) {
        let basePath = './public/spotimg/';
        let filename = Date.now() + '.png'
        let filePath = basePath + filename;
        // 存到文件夹
        console.log('新图片文件：' + filePath)
        const base64 = file.replace(/^data:image\/\w+;base64,/, "")
        const dataBuffer = Buffer.from(base64, 'base64')
        fs.writeFile(filePath, dataBuffer, function (err) {//用fs写入文件
            if (err) {
                console.log(err);
            } else {
                console.log("成功")
            }
        })
        imgurl = '../trip-koa/public/spotimg' + "/" + filename;
    }

    let sqlCount = 'select max(distinct id) as count from spot;';
    let num = await ctx.db.EXCUTE(sqlCount)
    let count = num[0].count;
    let id = count + 1;
    let city = ctx.request.body.city;
    let sqlCity = 'select id from city where name=?';
    let cityid = await ctx.db.EXCUTE(sqlCity, [city]);
    if (cityid.length != 0) {
        city = cityid[0].id;
        let name = ctx.request.body.name;
        let content = ctx.request.body.content;
        let image = imgurl;
        let sql = 'insert into spot(id,city,name,image,content) values(?,?,?,?,?)'
        let args = [
            id,
            city,
            name,
            image,
            content,
        ]
        let result = await ctx.db.EXCUTE(sql, args);
        ctx.body = { "code": 200, "msg": "success", status: "添加成功", data: [] }
    } else {
        ctx.body = { "code": 400, status: "可能输入省份信息有误", data: [] }
    }
})

//删除景点
router.post('/delspot', async (ctx, next) => {
    let id = ctx.request.body.id;
    //先查询保存本地存储的图片地址
    let selsql = `select image from spot where id=${id}`;
    let selresult = await ctx.db.EXCUTE(selsql, [id]);
    let fileimg = selresult[0].image;
    //删除数据库
    let delsql = 'delete from spot where id=?';
    let result = await ctx.db.EXCUTE(delsql, [id]);
    //删除对应的本地图片
    if (fileimg != '') {
        fileimg = fileimg.split("/")[4];
        let name = fileimg;
        let baseFilePath = './public/spotimg/';
        var files = [];
        if (fs.existsSync(baseFilePath)) {
            files = fs.readdirSync(baseFilePath);
            files.forEach(function (item) {
                var curPath = path.join(baseFilePath, fileimg);
                console.log(curPath)
                if (fs.statSync(curPath).isDirectory()) {//是否是目录
                    deleteFile(curPath);//递归删除目录
                } else {
                    if (item.indexOf(name) > -1) {
                        fs.unlinkSync(curPath); 18
                        console.log("删除文件：" + curPath);
                    }
                }
            });
        } else {
            console.log("给定的路径不存在！");
        }
    }
    ctx.body = { "code": 200, "msg": "ok", status: "删除成功" };
})

//修改景点
router.post('/editspot', async (ctx, next) => {
    let res = ctx.request.body
    let file = res.image;
    let imgurl;
    let id = ctx.request.body.id;
    if (file.split('/')[0] != '') {
        //先删除本地存储的原有图片
        let selsql = `select image from spot where id=${id}`;
        let selresult = await ctx.db.EXCUTE(selsql, [id]);
        let fileimg = selresult[0].image;
        fileimg = fileimg.split("/")[4];
        let name = fileimg;
        let baseFilePath = './public/spotimg/';
        var files = [];
        if (fs.existsSync(baseFilePath)) {
            files = fs.readdirSync(baseFilePath);
            files.forEach(function (item) {
                var curPath = path.join(baseFilePath, fileimg);
                console.log(curPath)
                if (fs.statSync(curPath).isDirectory()) {//是否是目录
                    deleteFile(curPath);//递归删除目录
                } else {
                    if (item.indexOf(name) > -1) {
                        fs.unlinkSync(curPath); 18
                        console.log("删除文件：" + curPath);
                    }
                }
            });
        } else {
            console.log("给定的路径不存在！");
        }

        //添加新的图片
        let basePath = './public/spotimg/';
        let filename = Date.now() + '.png'
        let filePath = basePath + filename;
        // 存到文件夹
        console.log(filePath)
        const base64 = file.replace(/^data:image\/\w+;base64,/, "")
        const dataBuffer = Buffer.from(base64, 'base64')
        fs.writeFile(filePath, dataBuffer, function (err) {//用fs写入文件
            if (err) {
                console.log(err);
            } else {
                console.log("成功")
            }
        })
        imgurl = '../trip-koa/public/spotimg' + "/" + filename;
    } else {
        file = file.split("/")[2];
        file = [file.split('.')[0], file.split('.')[2]];
        file = file.join('.')
        console.log(file)
        imgurl = '../trip-koa/public/spotimg' + "/" + file;
    }

    //存入数据库
    let name = ctx.request.body.name;
    let content = ctx.request.body.content;
    let image = imgurl;
    let sql = 'update spot set name=?,image=?,content=? where id=?'
    let args = [
        name,
        image,
        content,
        id,
    ]
    let result = await ctx.db.EXCUTE(sql, args);
    ctx.body = { "code": 200, "status": "修改成功" };
})

//上传图片
router.post('/uploadspot', async (ctx, next) => {
    ctx.body = { code: 200, status: 'success' }
})

module.exports = router